﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Windows.Forms;
using System.Diagnostics;

namespace Goa
{
    class Database
    {
        private string next;
        //MySqlDataReader mysqlReader;//, mysqlReader1;
        MySqlCommand editPatientCom;

        static string strProvider = "SERVER=delanoman.dyndns.info;" +
        "DATABASE=rhc;" +
        "UID=root;" +
        "PASSWORD=usbw;";

        MySqlConnection mysqlCon = new MySqlConnection(strProvider);

        public Database()
        {
            databaseInit();
        }

        private void databaseInit()
        {
            try
            {
                mysqlCon.Open();
            }
            catch (Exception er)
            {
                Debug.WriteLine("An Error Occured" + er.Message);
            }
        }

        public void addPatient(String voornaam, String achternaam, String geslacht, String username, String password)
        {
            MySqlCommand addPatientCom = new MySqlCommand("INSERT INTO  `rhc`.`patient` (`voornaam` ,`achternaam` ,`geslacht`, `username` ,`password`)VALUES ('" + voornaam + "',  '" + achternaam + "',  '" + geslacht + "','" + username + "',  '" + password + "');", mysqlCon);
            addPatientCom.ExecuteNonQuery();
            addPatientCom.Dispose();
        }

        public List<String> getPatient()
        {
            MySqlCommand mysqlCmd = new MySqlCommand("SELECT  `username` FROM  `patient`;" , mysqlCon);
            MySqlDataReader mysqlReader = mysqlCmd.ExecuteReader();
            List<String> list = new List<String>();
            while (mysqlReader.Read())
            {
                    list.Add(mysqlReader.GetString(0));
            }
            mysqlReader.Close();
            return list;
        }

        public Tuple<string, string, string> getUser(String username)
        {
            //hierin worden de user accounts opgehaald. Zodat deze eventueel kunnen worden vergeleken met reeds gebruikte user acounts.
            MySqlCommand mysqlCmd = new MySqlCommand("SELECT  `ID`, `voornaam`, `achternaam` FROM  `patient` WHERE  `username` =  '" + username + "'", mysqlCon);
            MySqlDataReader mysqlReader = mysqlCmd.ExecuteReader();
            String a = "";
            String b = "";
            String c = "";
            try
            {
                
                int i = 0;
                while (i < 1 && mysqlReader.Read())
                {
                    Debug.WriteLine(mysqlReader.GetString(0));
                    a = mysqlReader.GetString(0);
                    b = mysqlReader.GetString(1);
                    c = mysqlReader.GetString(2);
                    i++;
                }
            }
            catch (TimeoutException)
            {
                MessageBox.Show("Kan geen verbinding meer maken met de database.", "Database Timeout",
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }          
            

            Tuple<string, string, string> tuple = new Tuple<string, string, string>(a,b,c);
            mysqlReader.Close();
            return tuple;
        }

        public int getUserID(String username)
        {
            //hierin worden de user accounts opgehaald. Zodat deze eventueel kunnen worden vergeleken met reeds gebruikte user acounts.
            MySqlCommand mysqlCmd = new MySqlCommand("SELECT  `ID` FROM  `patient` WHERE  `username` =  '" + username + "'", mysqlCon);
            int a = 0;
            try
            {
                MySqlDataReader mysqlReader = mysqlCmd.ExecuteReader();
                int i = 0;
                while (i < 1 && mysqlReader.Read())
                {
                    a = mysqlReader.GetInt32(0);
                    i++;
                }
                mysqlReader.Close();
            }
            catch (TimeoutException)
            {
                MessageBox.Show("Kan geen verbinding meer maken met de database.", "Database Timeout",
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }

            return a;
        }


        public List<Patient> getUserList()
        {
            //hierin worden de user accounts opgehaald. Zodat deze eventueel kunnen worden vergeleken met reeds gebruikte user acounts.
            List<Patient> patientList = new List<Patient>();
            MySqlCommand mysqlCmd = new MySqlCommand("SELECT  * FROM  `patient`;", mysqlCon);
            MySqlDataReader mysqlReader = mysqlCmd.ExecuteReader();
            while (mysqlReader.Read())
            {
                Patient newPatient = new Patient(mysqlReader.GetString(0), mysqlReader.GetString(1), mysqlReader.GetString(2), mysqlReader.GetString(3), mysqlReader.GetString(4));
                patientList.Add(newPatient);
            }
            mysqlReader.Close();
            return patientList;
        }

        public List<Session> getSessionListByUserID(string id)
        {
            List<Session> sessionList = new List<Session>();
            MySqlCommand mysqlCmd = new MySqlCommand("SELECT  * FROM  `data` WHERE `ID` = " + id + ";", mysqlCon);
            MySqlDataReader mysqlReader = mysqlCmd.ExecuteReader();

            while (mysqlReader.Read())
            {
                Boolean contains = false;
                Session newSession = new Session(mysqlReader.GetInt32(1), id);
                foreach(Session s in sessionList){
                    if (s.SessionNumber == newSession.SessionNumber) {
                        contains = true;
                    }
                   }
                if (!contains) {
                    sessionList.Add(newSession);
                    contains = false;
                }
            }
            
            mysqlReader.Close();

            foreach (Session s in sessionList)
            {
                int sessie = s.SessionNumber;
                s.ListOfValues = getSessionList(sessie, id);
            }

            return sessionList;
        }


        private List<MeasuredValues> getSessionList(int sessienummer, string id)
        {
            MySqlCommand mysqlCmd = new MySqlCommand("SELECT  * FROM  `data` WHERE `ID` = " + id + " AND `sessienummer` = " + sessienummer + ";", mysqlCon);
            MySqlDataReader mysqlReader1 = mysqlCmd.ExecuteReader();
            List<MeasuredValues> measured = new List<MeasuredValues>();
            while (mysqlReader1.Read())
            {
                MeasuredValues m = new MeasuredValues(mysqlReader1.GetInt32(8), mysqlReader1.GetInt32(3), mysqlReader1.GetInt32(4), mysqlReader1.GetInt32(5), mysqlReader1.GetInt32(9), mysqlReader1.GetInt32(7), mysqlReader1.GetString(6));
                measured.Add(m);
            }
            mysqlReader1.Close();
            mysqlReader1.Dispose();
            mysqlReader1 = null;
            return measured;
        }
  
        public int getHighestSessionID(string id)
        {
            MySqlCommand mysqlCmd = new MySqlCommand("SELECT MAX(  `data`.`sessienummer` ) FROM  `rhc`.`data` WHERE  `ID` = " + id + ";", mysqlCon);
            MySqlDataReader mysqlReader = mysqlCmd.ExecuteReader();
            int sessionid = 0;
            while (mysqlReader.Read())
            {
                    sessionid = mysqlReader.GetInt32(0);
            }
            mysqlReader.Close();
            return sessionid;
        }

        public void closeDB()
        {
            mysqlCon.Close();
        }

        public void writeSession(String id, String sessienummer, String power, String timestamp, String rpm, String speed, String distance, String time, String energy, String pulse)
        {
            MySqlCommand writesession = new MySqlCommand("INSERT INTO  `rhc`.`data` (`ID` ,`sessienummer` ,`timestamp` ,`rpm` ,`speed` ,`distance` ,`time` ,`energy` ,`puls` ,`power`)VALUES ('" + id +"',  '"+sessienummer+"',  '"+timestamp+"',  '"+rpm+"',  '"+speed+"',  '"+distance+"',  '"+time+"',  '"+energy+"',  '"+pulse+"',  '"+power+"');", mysqlCon);
            writesession.ExecuteNonQuery();
        }

        public bool tryLogin(string username, string password)
        {
            bool accept = false;
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM patient WHERE username = '" + username + "' AND password = '" + password + "';", mysqlCon);
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Debug.WriteLine(reader.GetString(0));
                if (reader.GetString(0) == "")
                { accept = false; }
                else
                { accept = true; }
            }

            cmd.Connection.Close();
            reader.Dispose();
            cmd.Dispose();
            Debug.WriteLine(accept);
            return accept;
        }

        public void editPatient(string voornaam, string achternaam, string geslacht, string username, string password, String id, bool passChange)
        {
            if (passChange)
            {
                editPatientCom = new MySqlCommand("UPDATE  `rhc`.`patient` SET  `voornaam` =  '" + voornaam + "', `achternaam` =  '" + achternaam + "', `geslacht` =  '" + geslacht + "',`username` =  '" + username + "',`password` =  '" + password + "' WHERE  `patient`.`ID` = " + id + ";", mysqlCon);
            }
            else
            {
                editPatientCom = new MySqlCommand("UPDATE  `rhc`.`patient` SET  `voornaam` =  '" + voornaam + "', `achternaam` =  '" + achternaam + "', `geslacht` =  '" + geslacht + "',`username` =  '" + username + "' WHERE  `patient`.`ID` = " + id + ";", mysqlCon); 
            }
            editPatientCom.ExecuteNonQuery();
            
        }
    }
}

